﻿	
	   SET NOCOUNT ON;
	   DECLARE @Results TABLE
	  (
			[BackupTime] datetime,
			[Name] nvarchar(100)
	  );

      DECLARE @intProdVersion VARCHAR(1)
      SELECT @intProdVersion = CONVERT(VARCHAR(1), SERVERPROPERTY('productversion'))
      
      IF(@intProdVersion = 8)
            BEGIN
                  INSERT INTO @Results
                  SELECT 
                        MAX([msdb].[dbo].[backupset].[backup_finish_date]) AS [BackupTime],
                        [master].[dbo].[sysdatabases].[name]
                  FROM [master].[dbo].[sysdatabases]
                  LEFT JOIN [msdb].[dbo].[backupset] 
                  ON [msdb].[dbo].[backupset].[database_name] = [master].[dbo].[sysdatabases].[name] 
                  AND [msdb].[dbo].[backupset].[type] = @BackupType
                  WHERE ([master].[dbo].[sysdatabases].[name] NOT IN ('tempdb')) 
                  AND DATEDIFF(HOUR, [master].[dbo].[sysdatabases].[crdate], GETDATE()) >= @HourThreshold  -- Exclude any new Databases

                  -- To exclude other databases from this query, just add them to the comma delimited list
                  -- To exclude databases that are offline inlcude the following: AND ([master].[dbo].[sysdatabases].[status] <> 528)
                  -- For example ('tempdb', 'database1', 'database2')

                  GROUP BY [master].[dbo].[sysdatabases].[name]
      END
      ELSE
            BEGIN

                  INSERT INTO @Results
                  SELECT 
                        MAX([msdb].[dbo].[backupset].[backup_finish_date]) AS [BackupTime],
                        [msdb].[sys].[databases].[name]
                  FROM [msdb].[sys].[databases]
                  LEFT JOIN [msdb].[dbo].[backupset] 
                  ON [msdb].[dbo].[backupset].[database_name] = [sys].[databases].[name] 
                  AND [msdb].[dbo].[backupset].[type] = @BackupType 
                  AND [msdb].[dbo].[backupset].[is_snapshot] = @SnapShot
                  WHERE ([msdb].[sys].[databases].[name] NOT IN ('tempdb'))
				  AND [msdb].[sys].[databases].[state_desc] != 'OFFLINE' 
				  AND DATEDIFF(HOUR, [msdb].[sys].[databases].[create_date], GETDATE()) >= @HourThreshold -- Exclude any new Databases
                  
                  -- To exclude other databases from this query, just add them to the comma delimited list
                  -- For example ('tempdb', 'database1', 'database2')
				  -- To exclude databases that are offline inlcude the following: AND ([msdb].[sys].[databases].[state_desc] != 'OFFLINE')
                  
                  GROUP BY [msdb].[sys].[databases].[name]
            END
            
      DELETE @Results
      WHERE DATEDIFF(HOUR, [BackupTime], GETDATE()) <= @HourThreshold 

      SET NOCOUNT OFF;
      SELECT  * FROM @Results
